package cn.hnshxy.dch;

import java.sql.*;
import java.util.*;

public class DbHelper {
	private Connection conn=null;
	private PreparedStatement ps=null;
	private ResultSet rs=null;
	private String errMsg=null;
	public String getErrorMessage() {
		return errMsg;
	}
	static {
		System.out.println("加载驱动~");
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");//加载驱动
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
	/*
	 * 获取链接对象
	 * @return 返回和数据库间建立的链接
	 * @throws SQLException出现错误时抛出异常
	 */
	private Connection getConnection() throws SQLException {
		if(conn==null) {
			conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/db?serverTimezone=PRC&allowMultiQueries=true", "root", "12qwas");//创建链接
		}
		return conn;
	}
	/*
	 * 释放资源
	 */
	private void free() {
		System.out.println("释放资源~");
		try {
			if(rs!=null && !rs.isClosed()) rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			if(ps!=null && !ps.isClosed()) ps.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			if(conn!=null && !conn.isClosed()) conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		rs=null;
		ps=null;
		conn=null;
	}
	
	public int executeUpdate(String sql,Object...params) {
		try {
			conn=getConnection();
			ps=conn.prepareStatement(sql);
			for(int i=0;i<params.length;++i) {
				ps.setObject(i+1, params[i]);
			}
			return ps.executeUpdate();
		}catch (SQLException e) {
			errMsg=e.getMessage();
//			e.printStackTrace();
		} finally {
			free();
		}
		return -1;
	}
	
	public List<Map<String,Object>> executeQuery(String sql,Object...params) {
		try {
			conn=getConnection();
			ps=conn.prepareStatement(sql);
			for(int i=0;i<params.length;++i) {
				ps.setObject(i+1, params[i]);
			}
			rs=ps.executeQuery();
			return toList(rs);
		} catch (SQLException e) {
			errMsg=e.getMessage();
//			e.printStackTrace();
		} finally {
			free();
		}
		return null;
	}

	private List<Map<String, Object>> toList(ResultSet rs) throws SQLException {
		List<Map<String, Object>>list=new ArrayList<>();
		ResultSetMetaData md = rs.getMetaData();//元数据
		int count = md.getColumnCount();
		while(rs.next()) {
			Map<String, Object>fields=new HashMap<>();
			for(int i=1;i<=count;++i) {
				String label = md.getColumnLabel(i);
				Object value = rs.getObject(label);
				fields.put(label, value);
			}
			list.add(fields);
		}
		return list;
	}
	public static void main(String[] args) throws SQLException {
		DbHelper db = new DbHelper();
		List<Map<String, Object>> list = db.executeQuery("select t1 as 题目 from ku limit ?", 2);
		for(int i=0;i<list.size();++i) {
			Map<String, Object> map = list.get(i);
			Object t = map.get("题目");
			System.out.println(t);
		}
		System.out.println("Over");
	}
}